CHAPTER 5 USING BTRIEVE UTILITIES (continued) Concepts The following paragraphs describe concepts you should understand before using the Maintenance utility commands. Filenames The Maintenance utility runs as an NLM. The NLM environment does not recognize drive letters or environment variables. Thus, for commands that require a filename, the name must include the full pathname, such as SYS:\NWSQL\DEMODATA\PATIENTS.DTA. If you do not specify a volume, the utility assumes SYS: is the volume. Owner Names Btrieve allows you to restrict access to a file by specifying an owner name. Since owner names are optional, the files you use with this utility may or may not require an owner name. If the file requires an owner name, you must specify it using the /O option. (Alternatively, you can use a dash with this option, as in -O.) Owner names are case sensitive; that is, Sandy and SANDY are not considered to be the same. You can follow the /O option with an owner name or an asterisk (*). If you use an asterisk, the utility prompts you for an owner name. Description Files A description file is an ASCII file containing information that the Maintenance utility commands CREATE, INDEX, and SINDEX need to perform their operations. Description files contain one or more elements. An element consists of a keyword, followed by an equal sign (=), followed by a value (with no space separator). Each element in the description file corresponds to a particular characteristic of a Btrieve file or key definition. Appendix A, "Description Files," provides more information. Continuous Operation Continuous operation is a Btrieve feature that allows you to back up files while they are in use by Btrieve applications. Two Maintenance utility commands, STARTBU and ENDBU, begin and end continuous operation on a file or set of files. When continuous operation begins, Btrieve creates a temporary Btrieve file (called a delta file) for each data file in order to record any changes made to the data file while the backup is taking place. This temporary delta file may surpass the size of the original data file if users make extensive changes to the file during continuous operation. When continuous operation ends, Btrieve updates the master Btrieve files with the changes stored in the delta files. Btrieve deletes the delta files as soon as all applications close the corresponding Btrieve data files. NOTE: As indicated above, when you place a Btrieve file into continuous operation mode, Btrieve creates a temporary delta file with the same name as the data file but with a .^^^ extension. Therefore, do not create multiple Btrieve files with the same names but different extensions. For example, do not use a naming scheme such as INVOICE.HDR and INVOICE.DET for your Btrieve files. To back up files using continuous operation, first issue the BUTIL -STARTBU command, followed by the file or set of files. Next, run your backup program. To stop continuous operation, issue the BUTIL -ENDBU command. SUGGESTION: The best time to place Btrieve data files into continuous operation for backup is when the fewest users will be making modifications to the files. Command Files You can use a command file to do either of the following: o Execute a command that is too long to fit on the command line o Execute a command that you use often (by entering the command once in the command file and then executing the command file as often as you want) Command files contain the same information as that required on the command line. Rules for Command Files Observe the following rules when creating a Maintenance utility command file: o You must limit each line to 130 characters. NOTE: Lines longer than 130 characters could cause the server to end abnormally. For this reason, do not place long Maintenance utility commands in a server command (.NCF) file. o You cannot split a single parameter across two lines. o You can specify only one command per command file. Command File Example The following is an example command file, COPYPATS.CMD. The file calls the BUTIL -CLONE command to clone the file PATIENTS.DTA. -CLONE sys:\nwsql\demodata\allpats.dta sys:\nwsql\demodata\patients.dta The following command uses the COPYPATS.CMD file: LOAD BUTIL @sys:\nwsql\demodata\copypats.cmd Maintenance Utility Commands This section describes the Maintenance utility commands and explains when and how to use each one. At any time while using the Maintenance utility commands, you can enter LOAD BUTIL to see the Maintenance utility commands. The utility responds with the following screen. CLONE The CLONE command creates a new, empty Btrieve file with the same file specifications as an existing file (including any supplemental indexes, but excluding the owner name). The new Btrieve file includes all the defined key characteristics (such as key position, key length, or duplicate key values) contained in the existing file. Format LOAD BUTIL -CLONE outputBtrvFile sourceBtrvFile [/Oowner] outputBtrvFile The full pathname you want to use for the new, empty Btrieve file. sourceBtrvFile The full pathname of the existing Btrieve file that you want to replicate. owner The owner name, if any, for the source Btrieve file. The new Btrieve file will not have an owner name. Remarks Btrieve v6.x allows a maximum of 23 key segments in a Btrieve file with a page size of 1,024 bytes. Therefore, the CLONE command sets the page size in the new Btrieve file to 2,048 bytes if the existing Btrieve file contains 24 key segments and has a page size of 1,024 bytes. This occurs if the existing Btrieve file has a format earlier than Btrieve v6.0 and the Btrieve NLM was not loaded with the Create Btrieve Files in Pre v6.x Format configuration option. Example The following command creates the NEWAPP.DTA file by cloning the PATIENTS.DTA file. LOAD BUTIL -CLONE sys:\nwsql\demodata\newapp.dta sys:\nwsql\demodata\patients.dta SUGGESTION: If you are trying to recover from receiving Status Code 30 and you suspect that the header page of the source file might be damaged, try creating the new Btrieve file by using the BUTIL -CREATE command with a description file. CLROWNER The CLROWNER command clears the owner name of a Btrieve file. Format LOAD BUTIL -CLROWNER btrvFile /Oowner btrvFile The full pathname of the Btrieve file. owner The owner name to be cleared. Example The following command clears the owner name for the PATIENT1.DTA file. The owner name for the file is Sandy. LOAD BUTIL -CLROWNER sys:\nwsql\demodata\patient1.dta /OSandy COPY The COPY command copies the contents of one Btrieve file to another. COPY retrieves each record in the input Btrieve file and inserts it into the output Btrieve file. The record size must be the same in both files. After copying the records, COPY displays the total number of records inserted into the new Btrieve file. NOTE: COPY performs in a single step the same function as a RECOVER command followed by a LOAD command. By using the COPY command, you can create a Btrieve file that contains data from an old file, but has new key characteristics. To do so, proceed as follows: 1. Use the CREATE command to create an empty Btrieve file with the desired key characteristics (key position, key length, or duplicate key values). 2. Use the COPY command to copy the contents of the existing Btrieve file into the newly created Btrieve file. Format LOAD BUTIL -COPY inputBtrvFile outputBtrvFile [/Oowner1 [/Oowner2] ] inputBtrvFile The full pathname of the Btrieve file from which you want to transfer records. outputBtrvFile The full pathname of the Btrieve file into which you want to insert records. The output Btrieve file can contain data or be empty. owner1 The owner name of the input Btrieve file, if required. If only the output Btrieve file requires an owner name, specify /O followed by a blank for owner1 (as illustrated in the example). owner2 The owner name of the output Btrieve file, if required. Example The following command copies the records in PATIENTS.DTA to NEWPATS.DTA. The PATIENTS.DTA input file does not require an owner name, but the NEWPATS.DTA output file uses the owner name Pam. LOAD BUTIL -COPY sys:\nwsql\demodata\patients.dta sys:\nwsql\demodata\newpats.dta /O /OPam If you omit the first /O from this example, the utility assumes that the owner name Pam belongs to the input Btrieve file, not the output Btrieve file. CREATE The CREATE command generates an empty Btrieve file using the characteristics you specify in a description file. Before you can use the CREATE command, you must create a description file to specify the new key characteristics. For more information, see Appendix A, "Description Files." Format LOAD BUTIL -CREATE btrvFile descriptionFile btrvFile The full pathname of the Btrieve file you want to create. If the pathname is the name of an existing Btrieve file, this command creates a new, empty Btrieve file in place of the existing Btrieve file. Any data that was stored in the existing Btrieve file is lost and cannot be recovered. However, Btrieve does not create a new Btrieve file if you specify replace=n in the description file. (For an example, see "Replace Existing File" in Appendix A.) descriptionFile The full pathname of the description file containing the specifications for the new Btrieve file. Example The following command creates a Btrieve file named PATIENTS.DTA using the description provided in the BUILD.DES description file. LOAD BUTIL -CREATE sys:\nwsql\patients.dta sys:\nwsql\build.des Sample Description File for the CREATE Command The sample description file shown in the following illustration creates a Btrieve file. The Btrieve file is specified to have a page size of 512 bytes and 2 keys. The fixed-length portion of each record in the Btrieve file is set to 98 bytes. Variable-length records with no blank truncation, data compression, and Variable-tail Allocation Tables (VATs) are specified. The free space threshold is set to 20 percent. Allocation is set to 100 pages. Btrieve will preallocate 100 pages, or 51,200 bytes, when it creates the Btrieve file. --------------------------------------------------------------------------- record=98 variable=y truncate=n compress=y ---+ key=2 page=512 allocation=100 replace=n | File Specifications fthreshold=20 huge=y ---+ position=1 length=5 duplicates=y ---+ Key 0 modifiable=n type=string alternate=y | Segment 1 null=y value=20 segment=y ---+ position=6 length=10 duplicates=y ---+ Key 0 modifiable=n type=string alternate=y | Segment 2 null=y value=20 segment=n ---+ position=16 length=2 duplicates=n ---+ Key 1 modifiable=y type=numeric descending=y | alternate null=n segment=n ---+ name=path/upper.alt -------------------------------------------------------------------------- Key 0 is a segmented key with two duplicatable, nonmodifiable string segments and a null value of 20 hexadecimal (space) specified for both segments. Key 0 uses the collating sequence UPPER.ALT. Key 1 is a numeric, nonsegmented key that does not allow duplicates but permits modification. It is sorted in descending order. DROP The DROP command removes an index from a Btrieve file and adjusts the key numbers of any remaining indexes, subtracting 1 from each subsequent key number. If you do not want to renumber the keys, you can add 128 to the key number you specify to be dropped. This renumbering feature is available only for Btrieve v6.x files. Format LOAD BUTIL -DROP btrvFile keyNumber [/Oowner] btrvFile The full pathname of the Btrieve file from which you are dropping the index. keyNumber The number of the index you want to remove. If you want to preserve the original key numbers, add a 128 bias to the key number you specify. owner The owner name for the Btrieve file, if required. Examples In both of the following examples, PATIENTS.DTA has three keys. The original keys in the file were numbered 0, 1, and 2. In the first example, the BUTIL -DROP command drops key number 1 from the PATIENTS.DTA file and renumbers the remaining key numbers as 0 and 1. LOAD BUTIL -DROP sys:\nwsql\demodata\patients.dta 1 In the following example, the BUTIL -DROP command drops key number 1 and does not renumber the keys. The key numbers remain 0 and 2. LOAD BUTIL -DROP sys:\nwsql\demodata\patients.dta 129 ENDBU The ENDBU command ends continuous operation on a Btrieve file or set of Btrieve files previously defined for backup. Execute this command after you have issued the STARTBU command and your backup utility has finished running. (For more information on the STARTBU command, see "STARTBU." For more information on continuous operation, see "Continuous Operation.") To back up Btrieve files using continuous operation, first issue the LOAD BUTIL ŠSTARTBU command, followed by the Btrieve file or set of Btrieve files. Next, run your backup program. Then, stop continuous operation by issuing the LOAD BUTIL -ENDBU command. NOTE: When you place a Btrieve file into continuous operation mode, Btrieve creates a temporary delta file with the same name as the Btrieve data file, but with a .^^^ extension. Therefore, do not create multiple Btrieve files with the same names but different extensions. For example, do not use a naming scheme such as INVOICE.HDR and INVOICE.DET for your Btrieve files. Format LOAD BUTIL -ENDBU [btrvFile | @filename] btrvFile The full pathname of the Btrieve file for which you want to end continuous operation. @filename The name of a text file containing a list of Btrieve files for which you want to end continuous operation. The text file must contain the full pathname for each Btrieve file, and you must separate these pathnames with a space or a carriage return/line feed. Typically, this list of Btrieve files is the same as the list used with the STARTBU command. If you do not specify any Btrieve files with the LOAD BUTIL -ENDBU command, the utility stops continuous operation on all Btrieve files initialized by BUTIL -STARTBU and currently running in continuous operation mode. Example The following example ends continuous operation on the PATIENTS.DTA file. LOAD BUTIL -ENDBU sys:\nwsql\demodata\patients.dta INDEX The INDEX command builds an external index file for an existing Btrieve file, based on a field not previously specified as a key in the existing file. Before you can use the INDEX command, you must create a description file to specify the new key characteristics. (For more information on description files, see Appendix A, "Description Files.") The external index file created is a key-only Btrieve file. The records in the new file consist of the following: o The 4-byte address of each record in the existing Btrieve file o The new key value on which you want to sort NOTE: If the key length you specify in the description file is 10 bytes, the record length of the external index file would be 14 bytes (10 plus the 4-byte address). Format LOAD BUTIL -INDEX btrvFile indexFile descriptionFile [/Oowner] btrvFile The full pathname of the existing Btrieve file for which you want to build an external index. indexFile The full pathname of the index file in which Btrieve should store the external index. descriptionFile The full pathname of the description file you have created containing the new key definition. The description file should contain a definition for each segment of the new key. owner The owner name for the Btrieve file, if required. Remarks The INDEX command creates the external index file and then displays the number of records that were indexed. If you want to retrieve the Btrieve file's records using the external index file, use the SAVE command (described in "SAVE"). Sample Description File for the INDEX Command The description file shown in the following example defines a new key with one segment. The key begins at byte 30 of the record and is 10 bytes long. It allows duplicates, is modifiable, is a string type, and uses no alternate collating sequence. position=30 length=10 duplicates=y modifiable=y type=string alternate=n segment=n Example The following command creates an external index file called NEWPAT.IDX using a Btrieve file called PATIENTS.DTA. The PATIENTS.DTA file does not require an owner name. The description file containing the definition for the new key is called NEWIDX.DES. LOAD BUTIL -INDEX sys:\nwsql\demodata\patients.dta sys:\nwsql\demodata\newpat.idx sys:\nwsql\demodata\newidx.des NOTE: When you place a Btrieve file into continuous operation mode, Btrieve creates a temporary delta file with the same name as the data file, but with a .^^^ extension. Therefore, do not create multiple Btrieve files with the same names but different extensions. For example, do not use a naming scheme such as INVOICE.HDR and INVOICE.DET for your Btrieve files. LOAD The LOAD command inserts records from an input sequential file into a Btrieve file. LOAD performs no conversion on the data in the input sequential file. After the utility transfers the records to the Btrieve file, it displays the total number of records loaded. Before running the LOAD command, you must create the input sequential file and the Btrieve file. You can create the input sequential file using a standard text editor or an application; the input sequential file must have the required file format (as explained subsequently). You can create the Btrieve file using either BUTIL -CREATE or BUTIL -CLONE. Format LOAD BUTIL -LOAD inputFile btrvFile [/Oowner] inputFile The full pathname of the ASCII sequential file containing the records to be loaded into a Btrieve file. btrvFile The full pathname of the Btrieve file into which you want to insert the records. owner The owner name for the Btrieve file, if required. Required File Format Records in the input sequential file must be in the following format: o The first field must be a left-adjusted integer (in ASCII) that provides the length of the record. This field does not include the carriage return or line feed. For files with fixed-length records, the length you specify should equal the record length of the Btrieve file. For files with variable-length records, the length you specify must be at least as long as the minimum fixed length of the Btrieve file. o A separator (either a comma or a blank) must follow the length field. o The record data follows the separator. The length of the data must be the exact number of bytes specified by the length field. o A carriage return/line feed (0D0A hexadecimal) must terminate each line. The carriage return/line feed is not included in the length value at the beginning of the line, and LOAD does not insert the carriage return/line feed into the Btrieve file. o The last line in the file must consist of the end-of-file character (Ctrl+Z or 1A hexadecimal). The SAVE and RECOVER commands and most text editors automatically insert this character in the file. You can create an input sequential file using either a text editor or an application, as follows: o If you use a text editor to create the input sequential file, pad each record with blank spaces as necessary to fill the record to the length you specified at the beginning of the record. Fields containing binary data cannot be edited with most text editors. o If you use an application to create the input sequential file, append a carriage return/line feed to the end of each record and include an end-of-file character (Ctrl+Z or 1A hexadecimal) as the last line in the file. The sequential I/O calls provided by most high-level language processors insert carriage return, line feed, and end-of-file characters automatically. The following illustration shows the correct format for records in the input sequential file. For this example, the Btrieve file has a defined record length of 40 bytes. 40, The record follows the comma delimiter | | |____________________________________| | | | | Data | |__Carriage Return | | | or Line Feed | |__Comma Delimiter |__Blank Pad |____Record Length for Proper Length Example The following example loads sequential records from the PATIENTS.ADR file into the PATIENTS.DTA file. The owner name of the PATIENTS.ADR file is Sandy. LOAD BUTIL -LOAD sys:\nwsql\demodata\patients.adr sys:\nwsql\demodata\patients.dta /OSandy RECOVER The RECOVER command extracts data from a Btrieve file and places it in a sequential file that has the same format as the input sequential file used by the LOAD command. This is often useful for extracting some or all of the data from a damaged Btrieve file. The RECOVER command may be able to retrieve many, if not all, of the file's records. You can then use the LOAD command to insert the recovered records into a new, undamaged Btrieve file. NOTE: The Maintenance utility performs no conversion on the data in the records. Therefore, if you use a text editor to modify an output file containing binary data, be aware that some text editors may change the binary data, causing the results to be unpredictable. The RECOVER command performs the following actions: o Checks the file's Page Allocation Table (PAT) and reconstructs it, if users request this. The PAT is the part of the Btrieve file that maintains a map of each page's physical location. o Reads records in physical order from the Btrieve file, using Btrieve Step operations. o Creates a sequential file that is compatible with the required format for the LOAD command. (See "Required File Format" for more information about the format.) o Displays the total number of recovered records. Format LOAD BUTIL -RECOVER btrvFile outputFile [/Oowner] btrvFile The full pathname of the Btrieve file from which you want to recover data. outputFile The full pathname of the ASCII sequential file where the utility should store the recovered records. owner The owner name for the Btrieve file, if required. Remarks If the file's PAT is damaged, a prompt similar to the following appears: The file's Page Allocation Table seems to be damaged. BUTIL *strongly* recommends that you make a backup copy before continuing. Continue? 1=Yes 2=No By default, the prompt displays 2 (indicating No) on the next line. This allows you to exit the RECOVER command and back up the Btrieve file before proceeding. If you have already backed up the Btrieve file, enter 1 to continue running the RECOVER command. The RECOVER command allows you to set the Btrieve file's page size. It displays the following prompt: Enter the page size or 0 to quit: 512 The value displayed at this prompt is the result of an attempt to determine the original page size of the Btrieve file. If this value is incorrect, enter the correct page size. If you enter a page size that differs from the original page size, the result is unpredictable. If you are unsure of the correct page size, change the value as prompted by the utility. If the logical disk drive containing your output sequential file becomes full before the entire Btrieve file has been recovered, the utility stops, indicates the number of records already recovered, and displays the following prompt: The disk volume is full. Enter new file name to continue or a period to quit. To continue running the RECOVER command using an additional output sequential file, complete one of the following steps: o If you are recovering the Btrieve file to diskettes, remove the full diskette and replace it with another formatted diskette. o If you are recovering the Btrieve file to a hard disk, specify another logical disk drive that has space available. In either case, enter the full pathname of the Btrieve file you want to use to continue storing records, and then press the Enter key. The utility continues copying records from the Btrieve file to the new output sequential file. This process creates multiple sequential files that you must load separately with the LOAD command. If the RECOVER command receives a variable page error (Status Code 54), it places all the data it can obtain from the current record in the output sequential file and continues the recovery process. Upon completion, the utility displays a message similar to the following: 16 records recovered. Operation completed successfully. Example The following example extracts records from the PATIENTS.DTA file and writes them into the SEQPAT.DAT file. LOAD BUTIL -RECOVER sys:\nwsql\patients.dta sys:\nwsql\seqpat.dat SALVAGE The SALVAGE command examines the records in a file's PAT to determine if corruption has occurred. (The PAT maintains a map of the physical location of each page in the Btrieve file.) If corruption has occurred, the utility asks if you want to repair the PAT. Format LOAD BUTIL -SALVAGE btrvFile [/Oowner] btrvFile The full pathname of the Btrieve file containing the records you want to check. owner The owner name for the Btrieve file, if required. Remarks If the file's PAT is damaged, the utility reminds you that you should have a backup of the Btrieve file before proceeding and asks if you want to repair the file now. If you have already backed up the Btrieve file, enter Y (for Yes). If you have not backed up the Btrieve file, enter N (for No). After you enter Y, the utility asks you to enter a page size and provides you with the result of its attempt to determine the original page size. If you suspect that the value shown is incorrect, enter a new value. The utility then attempts to repair the Btrieve file, using the new value. If the utility cannot repair the Btrieve file, it sends a message identifying the reason why. NOTE: The SALVAGE command does not save the records to a sequential file. SAVE The SAVE command retrieves records from a Btrieve file using a specified index path and places them in a sequential file that is compatible with the required format for the LOAD command. You can then edit the sequential file and use the LOAD command to store the edited data in another Btrieve file. (See "LOAD" for more information about LOAD.) SAVE generates a single record in the output sequential file for each record in the input Btrieve file. Upon completion, SAVE displays the total number of records saved. NOTE: The Maintenance utility performs no conversion on the data in the records. Therefore, if you use a text editor to modify an output file containing binary data, be aware that some text editors may change the binary data, causing the results to be unpredictable. Format LOAD BUTIL -SAVE btrvFile outputFile [Y indexFile | N keyNumber] [/Oowner] btrvFile The full pathname of the Btrieve file containing the records you want to save. outputFile The full pathname of the ASCII sequential file in which you want the utility to store the records. indexFile The full pathname of an external index file by which you want to save records if you do not want to save records using the default of the lowest key number. keyNumber The key number (other than 0) by which you want to save records if you do not want to save records using the default of the lowest key number. owner The owner name for the Btrieve file, if required. Remarks If the logical disk drive containing your output sequential file becomes full before the entire Btrieve file has been saved, the utility stops, indicates the number of records already saved, and displays the following message: The disk volume is full. Enter new file name to continue or a period to quit. To continue the SAVE operation in another output sequential file, complete one of the following steps: o If you are saving the Btrieve file to diskettes, remove the full diskette and replace it with another formatted diskette. o If you are saving the Btrieve file to a hard disk, specify another logical disk drive that has space available. In either case, enter the full pathname of the Btrieve file you want to use to continue storing records, and press the Enter key. The utility continues copying records from the Btrieve file to the new output sequential file. Keep in mind that this process creates multiple sequential files that you must load separately with the LOAD command. Examples The following two examples illustrate how to use the SAVE command to retrieve records from a Btrieve file. The first example uses the NEWPAT.IDX external index file to retrieve records from the PATIENTS.DTA file and store them in an unformatted text file called PATIENTS.SAV: LOAD BUTIL -SAVE sys:\nwsql\demodata\patients.dta sys:\nwsql\demodata\patients.sav sys:\nwsql\demodata\newpat.idx The next example retrieves records from the PATIENTS.DTA file using key number 3 and stores them in an unformatted text file called PATIENTS.SAV: LOAD BUTIL -SAVE sys:\nwsql\demodata\patients.dta sys:\nwsql\demodata\patients.sav N 3 SETOWNER The SETOWNER command creates an owner for a Btrieve file. Format LOAD BUTIL -SETOWNER btrvFile /Oowner level btrvFile The full pathname of the Btrieve file. owner The owner name to be set. level The type of access restriction for the Btrieve file. The possible values for this parameter are as follows: 0 Requires an owner name for any access mode (no data encryption) 1 Permits read access without an owner name (no data encryption) 2 Requires an owner name for any access mode (with data encryption) 3 Permits read access without an owner name (with data encryption) Example The following example cretaes an owner for the PATIENTS.DTA file. The owner name is Sandy, and the restriction level is 1. LOAD BUTIL -SETOWNER sys:\nwsql\demodata\patients.dta\OSandy 1